package com.soa.database.util;

import java.lang.reflect.Type;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.google.gson.reflect.TypeToken;

public class JdbcUtil {
	/**
	 * 格式化sql
	 * 
	 * @param sql
	 * @param args
	 * @return
	 */
	public static JdbcSqlParam fmtTestSql(String sql, String serviceParams) {
		
		Type type = new TypeToken<List<Map<String,String>>>() {
		}.getType();
		List<Map<String,String>> commandParams = Json.formObject(type,
				serviceParams);
		Map<String,Map<String,String>> feildMap=new HashMap<String,Map<String,String>>();
		Map<String,Object> logicMap=new HashMap<String,Object>();
		for (Map<String,String> map : commandParams) {
			feildMap.put(map.get("feild"), map);
			logicMap.put(map.get("feild"), map.get("value"));
		}
		
		Pattern p_if = Pattern.compile("(\\$if\\{[^{}]*\\})+");
		Matcher m_if = p_if.matcher(sql);
		while (m_if.find()) {
			String rex = m_if.group(1);
			String key = rex.replace("$if{", "").replace("}", "").trim();
			String[] logic=key.split("\\|");
			Boolean flag=(Boolean)DyMethodUtil.invokeMethod(logic[0],logicMap);
			if(flag){
				String logc1=(String)DyMethodUtil.invokeMethod(logic[1],logicMap);
				sql = sql.replace(rex, logc1);
			}else{
				String logc2=(String)DyMethodUtil.invokeMethod(logic[2],logicMap);
				sql = sql.replace(rex, logc2);
			}
		}
		
		Pattern p = Pattern.compile("(\\$\\{[^{}]*\\})+");
		Matcher m = p.matcher(sql);
		List<Object> al=new ArrayList<Object>();
		while (m.find()) {
			String rex = m.group(1);
			String key = rex.replace("${", "").replace("}", "").trim();
			sql = sql.replace(rex, "?");
			al.add(feildMap.get(key).get("value"));
		}
		Object[] args = al.toArray();
		JdbcSqlParam param = new JdbcSqlParam();
		param.setSql(sql);
		param.setArgs(args);
		return param;
	}
	
	
	/**
	 * 格式化sql
	 * 
	 * @param sql
	 * @param args
	 * @return
	 */
	public static JdbcSqlParam fmtSql(String sql, String serviceParams) {
		Map<String,Object> commandParams = Json.formMapByObject(serviceParams);
    	Pattern p_if = Pattern.compile("(\\$if\\{[^{}]*\\})+");
		Matcher m_if = p_if.matcher(sql);
		while (m_if.find()) {
			String rex = m_if.group(1);
			String key = rex.replace("$if{", "").replace("}", "").trim();
			String[] logic=key.split("\\|");
			Boolean flag=(Boolean)DyMethodUtil.invokeMethod(logic[0],commandParams);
			if(flag){
				String logc1=(String)DyMethodUtil.invokeMethod(logic[1],commandParams);
				sql = sql.replace(rex, logc1);
			}else{
				String logc2=(String)DyMethodUtil.invokeMethod(logic[2],commandParams);
				sql = sql.replace(rex, logc2);
			}
		}
		Pattern p = Pattern.compile("(\\$\\{[^{}]*\\})+");
		Matcher m = p.matcher(sql);
		
		List<Object> al=new ArrayList<Object>();
		while (m.find()) {
			String rex = m.group(1);
			String key = rex.replace("${", "").replace("}", "").trim();
			sql = sql.replace(rex, "?");
			al.add(commandParams.get(key));
		}
		Object[] args = al.toArray();
		JdbcSqlParam param = new JdbcSqlParam();
		param.setSql(sql);
		param.setArgs(args);
		return param;
	}
	
	public static String getPageSQL(String sql,int startIndex ,int lastIndex ,String type){
		if("1".equals(type)){
			StringBuffer paginationSQL = new StringBuffer(" SELECT top 1000 * FROM ( ");
			paginationSQL.append(sql);
			paginationSQL.append(") a");
			return paginationSQL.toString();
		}
		//oracle
		if("2".equals(type)){
			StringBuffer paginationSQL = new StringBuffer(" SELECT * FROM ( ");
			paginationSQL.append(" SELECT temp.* ,ROWNUM num FROM ( ");
			paginationSQL.append(sql);
			paginationSQL.append("　) temp where ROWNUM <= " + lastIndex);
			paginationSQL.append(" ) WHERE　num > " + startIndex);
			return paginationSQL.toString();
		}
		//db2
		if("3".equals(type)){
			StringBuffer paginationSQL = new StringBuffer(" SELECT * FROM ( ");
			paginationSQL.append("select a.*,rownumber() over() as rowid from (");
			paginationSQL.append(sql);
			paginationSQL.append(") a ) tmp where tmp.rowid >"+startIndex+" and  tmp.rowid <="+lastIndex+"");
			System.out.println(paginationSQL.toString());
			return paginationSQL.toString();
		}
		//mysql
		if("4".equals(type)){
			int pageSize=lastIndex-startIndex;
			StringBuffer paginationSQL = new StringBuffer(" SELECT * FROM ( ");
			paginationSQL.append(sql);
			paginationSQL.append(") a LIMIT "+startIndex+", "+pageSize+"");
			return paginationSQL.toString();
			
		}
		//pgsql
		if("6".equals(type)){
			int pageSize=lastIndex-startIndex;
			StringBuffer paginationSQL = new StringBuffer(" SELECT * FROM ( ");
			paginationSQL.append(sql);
			paginationSQL.append(") a LIMIT "+pageSize+" offset "+startIndex+"");
			return paginationSQL.toString();
			
		}
		return null;
	}
}
